<?
	session_start();
	include("../lib/function.php");
	include("../lib/header.php");
	include("../lib/config.php");
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Pencarian Detail Pelamar</title>
<link rel="stylesheet" type="text/css" href="../style/style.css" />
<script type="text/javascript">
	function show_popup(page)
	{
		child = open
		(
			page,
			"",
			"scrollbars,width=400,height=400,left=150,top=150"
		);
	}
</script>
<script language="javascript" src="<?=$path?>/js/calendar.js"></script>
</head>
<?
	$show		= $HTTP_GET_VARS['show'];
	$page 		= $HTTP_GET_VARS['page']; // get nomor halaman yang dipanggil
	$next		= $HTTP_GET_VARS['next']; // get segmen halaman yang dipanggil
	$pendidikan = $HTTP_GET_VARS['pendidikan'];
	$pengalaman	= $HTTP_GET_VARS['pengalaman'];
	$nama		= $HTTP_GET_VARS['nama'];
	$alamat		= $HTTP_GET_VARS['alamat'];
	$skill		= $HTTP_GET_VARS['skill'];
	$dari		= $HTTP_GET_VARS['dari'];
	$sampai		= $HTTP_GET_VARS['sampai'];
	$tgl_awal	= $HTTP_GET_VARS['tgl_awal'];
	$tgl_akhir	= $HTTP_GET_VARS['tgl_akhir'];
?>
<body>
<fieldset>
 <legend>Pencarian Detail Pelamar</legend>
 <form action="<?=$PHP_SELF?>" method="get" name="f1">
 <table align="center" width="97%" border="0" cellpadding="2" cellspacing="1">
  <tr>
   <td colspan="5">Pencarian Berdasarkan:</td>
  </tr>
  <tr>
   <td>Nama Pelamar:</td>
   <td><input type="text" name="nama" size="35" /></td>
   <td>&nbsp;</td>
   <td>Usia</td>
   <td><input type="text" name="dari" size="5" maxlength="5" /> S/D <input type="text" name="sampai" maxlength="5" size="5" /> (tahun)</td>
  </tr>
  <tr>
   <td>Alamat:</td>
   <td><input type="text" size="35" name="alamat" /></td>
   <td>&nbsp;</td>
   <td>Pendidikan</td>
   <td>
	   <select name='pendidikan'>
    	<option value=''>--- Silahkan Pilih ---</option>
		<?
		$sqlP = mssql_query("SELECT * FROM MstPendidikan ORDER BY keterangan,jurusan ASC",$conn);
		while($rowP = mssql_fetch_array($sqlP)){
			echo"<option value='$rowP[Pend_ID]'>$rowP[keterangan] - $rowP[jurusan]</option>";
		}
		?>
	   </select>
   </td>
  </tr>
  <tr>
   <td>Pengalaman Kerja</td>
   <td><input type="text" name="pengalaman" size="35" /></td>
   <td>&nbsp;</td>
   <td>Skill</td>
   <td><input type="text" name="skill" size="35" /></td>
  </tr>
  <tr>
   <td>Periode Tanggal Lamaran</td>
   <td colspan="4">
    <input type="text" name="tgl_awal" size="11" maxlength="10" />
    <a href="javascript:cal1.popup();"><img src="<?=$path?>/images/cal.gif" width="16" height="16" border="0" alt="Kalendar" title="Kalendar"></a> 
    s.d
    <input type="text" name="tgl_akhir" size="11" maxlength="10" />
    <a href="javascript:cal2.popup();"><img src="<?=$path?>/images/cal.gif" width="16" height="16" border="0" alt="Kalendar" title="Kalendar"></a> 
   </td>
  </tr>
  <tr>
   <td colspan="3">&nbsp;</td>
   <td colspan="2">
   <input type="submit" value="Cari" class="button" />
   <input type="reset" value="Reset" class="button" />
   <input type="button" onclick="Javascript:location.href='exp_excel_pel.php?nama=<?=$nama?>&amp;alamat=<?=$alamat?>&amp;pengalaman=<?=$pengalaman?>&amp;skill=<?=$skill?>&amp;pendidikan=<?=$pendidikan?>&amp;dari=<?=$dari?>&amp;sampai=<?=$sampai?>&amp;tgl_awal=<?=$tgl_awal?>&amp;tgl_akhir=<?=$tgl_akhir?>'" value="Kirim ke Excel" class="button" /></td>
  </tr>
 </table>
 </form>
</fieldset><br />
<?
	$sql  = "WITH ASD AS (select *, year(getdate()) - year(Tgl_Lahir) Usia from karyawan A where pelamar='1' ";
	
	if(!empty($HTTP_GET_VARS['pendidikan'])){
		$sql .= " AND A.NIK in (SELECT NIK FROM Pendidikan WHERE Pend_ID='".$HTTP_GET_VARS['pendidikan']."' AND NIK=A.NIK)";
	}
	
	if(!empty($HTTP_GET_VARS['pengalaman'])){
		$sql .= " AND A.NIK in (SELECT NIK FROM Karir WHERE (jabatan LIKE '%".strtoupper($HTTP_GET_VARS['pengalaman'])."%' OR instansi LIKE '%".$HTTP_GET_VARS['pengalaman']."%') AND NIK=A.NIK) ";
	}
	
	if(!empty($HTTP_GET_VARS['skill'])){
		$sql .= " AND A.NIK in (SELECT NIK FROM Kursus WHERE Program LIKE '%".strtoupper($HTTP_GET_VARS['skill'])."%' AND NIK=A.NIK)";
	}
	
	$sql .= " ) SELECT count(*) FROM ASD";
	
	$sql .= " WHERE Nama LIKE '%".strtoupper($HTTP_GET_VARS['nama'])."%' AND Alamat LIKE '%".strtoupper($HTTP_GET_VARS['alamat'])."%'";

	if(!empty($HTTP_GET_VARS['dari']) || !empty($HTTP_GET_VARS['sampai'])){
		$sql .= " AND ((year(getdate()) - year(Tgl_Lahir)) BETWEEN '".$HTTP_GET_VARS['dari']."' AND '".$HTTP_GET_VARS['sampai']."') ";
	}
	
	// pencarian berdasarkan periode tanggal
	// 01/12/2007
	// 01234567890
	// 
	$t1 = $_GET['tgl_awal'];
	$t2 = $_GET['tgl_akhir'];
	if(!empty($t1) && !empty($t2)){
		// konversi tanggal awal dmy to mdy
		$d = substr($t1,0,2);
		$m = substr($t1,3,2);
		$y = substr($t1,6,4);
		
		$taw = $m."/".$d."/".$y; 
		
		$d1 = substr($t2,0,2);
		$m1 = substr($t2,3,2);
		$y1 = substr($t2,6,4);
		
		$tak = $m1."/".$d1."/".$y1; 
		
		$sql .= " AND (datecreated between '".$taw."' and '".$tak."') ";
	}

	//echo $sql;
	$query = mssql_query($sql,$conn);
	
	//$result	= mssql_query("SELECT count(*) FROM Karyawan",$conn);
	$total	= mssql_fetch_row($query);
	$limit	= 10;
	$pager  = Pager::getPagerData($total[0], $limit, $page);
	$offset = $pager->offset;
	$limit  = $pager->limit;
	$page   = $pager->page;

	if($total > 0){
?>
<fieldset><legend>Hasil Pencarian</legend>
<?
	PagingPel($pager,$page,$next,$limit,$nama,$alamat,$pengalaman,$dari,$sampai,$pendidikan,$skill,$tgl_awal,$tgl_akhir);
	//echo"$sql";
?>
<table width="100%">
 <tr class="headlist">
  <td>No</td>
  <td>NIK</td>
  <td>Nama</td>
  <td>Alamat</td>
  <td>Usia</td>
  <td>Pendidikan</td>
  <td>Pengalaman</td>
  <td>Kursus</td>
  <td>Tgl Lamaran</td>
 </tr>
 	<?
	$sqlccc  = "WITH ASD1 AS (select ROW_NUMBER() OVER(ORDER BY pelamar) AS No, A.NIK,Nama,Alamat, (year(getdate()) - year(Tgl_Lahir)) as Usia, pelamar, convert(char(12), DateCreated, 103) tgl_lamaran, datecreated from karyawan A where pelamar='1' ";
	
	if(!empty($HTTP_GET_VARS['pendidikan'])){
		$sqlccc .= " AND A.NIK in (SELECT NIK FROM Pendidikan WHERE Pend_ID='".strtoupper($HTTP_GET_VARS['pendidikan'])."' AND NIK=A.NIK)";
	}
	
	if(!empty($HTTP_GET_VARS['pengalaman'])){
		$sqlccc .= " AND A.NIK in (SELECT NIK FROM Karir WHERE (jabatan LIKE '%".strtoupper($HTTP_GET_VARS['pengalaman'])."%' OR instansi LIKE '%".$HTTP_GET_VARS['pengalaman']."%') AND NIK=A.NIK) ";
	}
	
	if(!empty($HTTP_GET_VARS['skill'])){
		$sqlccc .= " AND A.NIK in (SELECT NIK FROM Kursus WHERE Program LIKE '%".strtoupper($HTTP_GET_VARS['skill'])."%' AND NIK=A.NIK)";
	}

	$sqlccc .= " AND Nama LIKE '%".strtoupper($HTTP_GET_VARS['nama'])."%' AND Alamat LIKE '%".strtoupper($HTTP_GET_VARS['alamat'])."%'";

	if(!empty($HTTP_GET_VARS['dari']) || !empty($HTTP_GET_VARS['sampai'])){
		$sqlccc .= " AND ((year(getdate()) - year(Tgl_Lahir)) BETWEEN '".$HTTP_GET_VARS['dari']."' AND '".$HTTP_GET_VARS['sampai']."') ";
	}

	// pencarian berdasarkan periode tanggal
	// 01/12/2007
	// 01234567890
	// 
	$t1 = $_GET['tgl_awal'];
	$t2 = $_GET['tgl_akhir'];
	if(!empty($t1) && !empty($t2)){
		// konversi tanggal awal dmy to mdy
		$d = substr($t1,0,2);
		$m = substr($t1,3,2);
		$y = substr($t1,6,4);
		
		$taw = $m."/".$d."/".$y; 
		
		$d1 = substr($t2,0,2);
		$m1 = substr($t2,3,2);
		$y1 = substr($t2,6,4);
		
		$tak = $m1."/".$d1."/".$y1; 
		
		$sqlccc .= " AND (datecreated between '".$taw."' and '".$tak."') ";
	}

	$sqlccc.= " ) SELECT * FROM ASD1 WHERE No BETWEEN $offset AND ".($limit+$offset)."";
	
	//echo $sqlccc;
		$sqlcccd = mssql_query($sqlccc,$conn);
    	while($row = mssql_fetch_array($sqlcccd)){
			echo"
			 <tr class=contentlist>
			  <td align=right>$row[No]</td>
			  <td>$row[NIK]</td>
			  <td>$row[Nama]</td>
			  <td>$row[Alamat]</td>
			  <td>$row[Usia]</td>";
		// get pendidikan
			$sqlpend = mssql_query("	
			select NIK, A.Pend_ID, CASE keterangan 
				WHEN 'SD' THEN '1' 
				WHEN 'SD Lain-lain' THEN '1' 
				WHEN 'SMP' THEN '2' 
				WHEN 'SMP Lain-lain' THEN '2' 
				WHEN 'SMA' THEN '3' 
				WHEN 'SMK' THEN '3' 
				WHEN 'SMEA' THEN '3' 
				WHEN 'STM' THEN '3' 
				WHEN 'SMKK' THEN '3' 
				WHEN 'ST' THEN '3' 
				WHEN 'D1' THEN '4' 
				WHEN 'D2' THEN '5' 
				WHEN 'D3' THEN '6' 
				WHEN 'D4' THEN '7' 
				WHEN 'S1' THEN '8' 
				WHEN 'S2' THEN '9' 
				WHEN 'S3' THEN '10' 
			END jenjang, keterangan, jurusan  from pendidikan A, mstpendidikan B where A.Pend_ID=B.Pend_ID AND NIK='$row[NIK]' ORDER BY jenjang DESC",$conn);
			$rowpend = mssql_fetch_array($sqlpend);
			  echo"<td>$rowpend[keterangan], $rowpend[jurusan]</td>";
			  echo"<td>";
			  // get pengalaman
			  $skarir = mssql_query("SELECT * FROM Karir WHERE NIK='$row[NIK]'");
			  while($rkarir = mssql_fetch_array($skarir)){
			  	echo"&bull; $rkarir[jabatan], $rkarir[instansi] <br>";
			  }
			echo"</td><td>";
			  // get kursus
			  $skursus = mssql_query("SELECT * FROM Kursus WHERE NIK='$row[NIK]'");
			  while($rkursus = mssql_fetch_array($skursus)){
			  	echo"&bull; $rkursus[Program] <br>";
			  }
			echo"</td>  
			<td>$row[tgl_lamaran]</td>
			 </tr>
			";
		}
	?>
</table>
<?
	}
?>
<script type="text/javascript">
// script untuk popup calendar
	var cal1 = new calendar1(document.forms['f1'].elements['tgl_awal']);
	cal1.year_scroll = true;
	cal1.time_comp = false;

	var cal2 = new calendar1(document.forms['f1'].elements['tgl_akhir']);
	cal2.year_scroll = true;
	cal2.time_comp = false;
	
</script>
</fieldset>
</body>
</html>
